* =======================================================================================================================================================
*
* Code Description: 
* This codefile generates the necessary data for and the results of Table 6 (security-level effects during March 2020)
*
* =======================================================================================================================================================
*
* Major output:
* Table 6: Security-level effects.
* =======================================================================================================================================================
*
* General disclaimer:
* This file directory produces replication code for "Connected Funds". 
* Because we cannot share the underlying data provided by the Bundesbank's Research Data and Service Centre (RDSC) and other subscription data sources, 
* we have included pseudo data to show how the raw data are formatted. 
* Other researchers can go through a similar approval and subscription process to obtain the underlying data. (2023-03-23)
*
* =======================================================================================================================================================


// Paths
global Path "C:\ConnectedFunds_Codebase\"
global Codes "Codes" 
sysdir set STBPLUS "${Path}Code\Ado" 

clear
est clear

cd "${Path}Data\CSDB\"


global sample ""


****** data preparation
use "${Path}Data\SecurityLevelEffects\SecurityLevel_Returns_Full.dta"

gen year = floor(datum/100)
gen month = datum - 100*year

gen datem = ym(year,month)
format datem %tm

drop if nfunds_lookthrough == .
drop if return_eur == .

egen ui_isin = group(isin)
sort ui_isin datem
xtset ui_isin datem

* add vix
* (Source: https://www.cboe.com/tradable_products/vix/vix_historical_data/)

merge m:1 datum using "${Path}Data\VIX\VIX_monthly.dta" 
keep if _merge == 3
drop _merge

* add 5 factors (equities)
* http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html#International
merge m:1 datem using "${Path}Data\FactorExposures\Europe_5_Factors.dta" 
keep if _merge == 3
drop _merge

* add bond factors 
* Source: http://www.jenniebai.com/uploads/1/4/1/6/14160412/bondfactorall_2021.xlsx
merge m:1 datem using "${Path}Data\FactorExposures\bondfactorall_2021.dta" 
keep if _merge == 3
drop _merge

* add Moskowitz factors
* Source: https://www.aqr.com/Insights/Datasets/Century-of-Factor-Premia-Monthly
merge m:1 datem using "${Path}Data\FactorExposures\Century of Factor Premia Monthly.dta", keepusing(Fixedincome*)
keep if _merge == 3
drop _merge

*** load exchange rates to convert returns to USD (source: ECB, https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip?dc369dd37e0f7acca72522b9d0017e49)
merge m:1 datem using "${Path}Data\FactorExposures\usd.dta"
keep if _merge == 3 
drop _merge

* drop assets with stale quotes (more than 50% of all obs. with exactly zero returns)
gen tmp = 0
replace tmp = 1 if return_cleanprice_eur == 0
bys ui_isin: egen tmp_zero = sum(tmp)
bys ui_isin: gen  tmp2 = _N
gen tmp_sh = tmp_zero/tmp2

keep if tmp_sh < 0.5
drop tmp* 


gen return_cleanprice_usd = return_cleanprice_eur + r_usd
replace return_cleanprice_eur = return_cleanprice_usd

******

gen asset_type = .

* equities
replace asset_type = 1 if regexm(ESA_INS_2010,"_51") == 1

* corporate bonds
replace asset_type = 2 if regexm(ESA_INS_2010,"_3") == 1
* sovereign bonds
replace asset_type = 3 if regexm(ESA_ISSUER_2010,"_13") == 1 & asset_type == 2

* fund shares
replace asset_type = 4 if regexm(ESA_INS_2010,"_52") == 1

egen tmp = min(asset_type), by(isin)
replace asset_type = tmp

replace asset_type = . if asset_type == 1 & DEBT_TYPE != ""

* filter: only straight bonds
replace asset_type = . if asset_type == 2 & DEBT_TYPE != "D.11"
replace asset_type = . if asset_type == 2 & COUPON_TYPE != "FIX"
replace asset_type = . if asset_type == 3 & DEBT_TYPE != "D.11"
replace asset_type = . if asset_type == 3 & COUPON_TYPE != "FIX"

* keep only the three relevant asset classes
drop if tmp == .
drop if tmp == 4 
drop tmp

*******************

sort ui_isin datem
xtset ui_isin datem


********************************* calculate factor loadings *********************************

* some rough cleaning before factor estimation
drop if return_cleanprice_eur > 2
drop if return_cleanprice_eur < -0.9

replace return_cleanprice_eur = 100*return_cleanprice_eur

*******************************  equities

// Commpute excess returns 
gen xret = return_cleanprice_eur - rf 

// Fama French model (5 factor)
tsset ui_isin datem

// Fast rolling regression
fastrollreg xret mktrf smb hml rmw cma, /// 
	 window(24) increment(1) panelvar(ui_isin) coeff(b_alphaFama b_mktrf b_smb b_hml b_rmw b_cma) /// 
	 rsq(r2_Fama5) err(err_Fama5) obs(obs_Fama5) se(se_b_alpha se_b_mktrf se_b_smb se_b_hml se_b_rmw se_b_cma)
	 
*******************************  bonds

// Bai et al
tsset ui_isin datem

// Fast rolling regression
fastrollreg xret MKTbond DRF CRF LRF, /// 
	 window(24) increment(1) panelvar(ui_isin) coeff(b_alphaBai b_MKTbond b_DRF b_CRF b_LRF) /// 
	 rsq(r2_Bai) err(err_Bai) obs(obs_Bai) se(se_b_alphaBai se_b_MKTbond se_b_DRF se_b_CRF se_b_LRF)

	 
* Moskowitz	 - fixed income
rename FixedincomeValue moskF_Value
rename FixedincomeMomentum moskF_Mom
rename FixedincomeCarry moskF_Carry
rename FixedincomeDefensive moskF_Def
rename FixedincomeMarket moskF_MKT

fastrollreg xret moskF_MKT moskF_Def moskF_Carry moskF_Mom moskF_Value, /// 
	 window(24) increment(1) panelvar(ui_isin) coeff(b_alpha_moskF b_moskF_MKT b_moskF_Def b_moskF_Carry b_moskF_Mom b_moskF_Value) /// 
	 rsq(r2_moskF) err(err_moskF) obs(obs_moskF)
	 
drop if err_Fama5 == 999
drop if obs_Fama5 == .
drop *b_alpha* 


********************************* calculate lagged factor loadings *********************************

foreach x of varlist b_* {
	gen lag_`x' = l.`x'
}


*** rolling-window alpha
gen alpha_Fama5 = xret - ///
					(lag_b_mktrf* mktrf + lag_b_smb * smb + ///
					 lag_b_hml* hml + lag_b_rmw* rmw  + lag_b_cma* cma)				
					 
gen alpha_Bai = xret - ///
					(lag_b_MKTbond* MKTbond + lag_b_DRF * DRF + ///
					 lag_b_CRF* CRF + lag_b_LRF* LRF)	
					 

gen alpha_MoskowitzF = xret - ///
					(lag_b_moskF_MKT * moskF_MKT + lag_b_moskF_Mom * moskF_Mom + lag_b_moskF_Def * moskF_Def + ///
					lag_b_moskF_Carry * moskF_Carry + lag_b_moskF_Value * moskF_Value) 
					
drop xret 


************************************ now: run the actual regressions for shorter subsample ************************************


keep if datem >=tm(2019m1)

egen ui_date = group(datem)
 
replace totalholdings = 1000*totalholdings
replace indirect_minus_direct = 1000*indirect_minus_direct
gen sh_tmp = indirect_minus_direct/totalholdings
replace sh_tmp = 1 if sh_tmp > 1

gen log_holdings = log(totalholdings)
gen totaloutstanding = AMOUNT_OUTST_EUR
replace totaloutstanding = MARKET_CAP_EUR if totaloutstanding == .
gen sh_holdings = 100*totalholdings/totaloutstanding
keep if sh_holdings <= 110
replace totaloutstanding = totalholdings if sh_holdings > 100
replace sh_holdings = totalholdings/totaloutstanding
gen lag_sh_holdings = L.sh_holdings

gen log_totaloutstanding = log(totaloutstanding)
gen age = (EXTRACTION_DT - ISSUE_DT)/365
gen lag_log_totaloutstanding = L.log_totaloutstanding

keep if age >=0
drop if age == .

*** drop small assets
egen tmp = mean(totaloutstanding), by(ui_isin)
keep if tmp > 10^6
drop tmp


**** use Moskowitz alpha for sovereign bonds
replace alpha_Bai  = alpha_MoskowitzF  if asset_type == 3


*** developed market indicator
global list_all   "AU AT BE CA CY DK FI FR DE HK IE IL IT JP LU NL NZ NO PL SG KR ES SE CH US GB"	
global list_IMF   "CA US AT BE CZ DK ES FI FR DE GR IC IE IS IT LT LI NL NO PO SL SK ES SW CH UK AU JP KR NZ"
 
 
/* all definition */
gen issuer_list_all = 0
foreach c of global list_all{
		di "`c'"
		replace issuer_list_all = 1 if ISSUER_COUNTRY == "`c'"
}

/* IMF definition */
gen issuer_list_IMF = 0
foreach c of global list_IMF{
		di "`c'"
		replace issuer_list_IMF = 1 if ISSUER_COUNTRY == "`c'"
}

gen developed = issuer_list_all

gen dum_developed = 0
replace dum_developed = 1 if developed == 1

local controls = "lag_log_totaloutstanding  lag_sh_holdings"

* winsorize return and display in percent

foreach x of varlist return_cleanprice_eur alpha_* alphap_* {
	winsor2 `x', cuts(1 99)  by(asset_type) suffix(W)
	
	sort ui_isin datem

	gen lag_`x' = l.`x'W
}

gen lag_return_cleanprice_eurW = l.return_cleanprice_eurW
 
xtset ui_isin datem

gen lag_BOND_DURATION  = l.BOND_DURATION

************************************************************************
**** regression - covid only
************************************************************************

est clear

preserve 

keep if datem >= tm(2019m12)

** balanced panel
bys ui_isin: gen tmp_num = _N
summ tmp_num
keep if tmp_num == r(max)

********************

gen Dummy_March2020 = 0
replace Dummy_March2020 = 1 if datem == tm(2020m3)

** this is a prelim step to identify the treatment group
gen tmp2 = sh_tmp 
replace tmp2 = . if datem > tm(2019m12)
egen tmp2_sh = max(tmp2), by(ui_isin)

gen tmp3 = sh_holdings
replace tmp3 = . if datem > tm(2019m12)
egen tmp3_sh = max(tmp3), by(ui_isin)
summ tmp3_sh, det
** keep only assets where DE funds hold at least 1% of the total outstanding
keep if tmp3_sh >= 0.01

*** this is the treatment variable
gen IsCrossHeld = .
summ tmp2_sh if asset_type == 1, det
replace IsCrossHeld = 0 if tmp2_sh <= r(p50) & asset_type == 1
replace IsCrossHeld = 1 if tmp2_sh > r(p50) & asset_type == 1
summ tmp2_sh if asset_type == 2, det
replace IsCrossHeld = 0 if tmp2_sh <= r(p50) & asset_type == 2
replace IsCrossHeld = 1 if tmp2_sh > r(p50) & asset_type == 2
summ tmp2_sh if asset_type == 3, det
replace IsCrossHeld = 0 if tmp2_sh <= r(p50) & asset_type == 3
replace IsCrossHeld = 1 if tmp2_sh > r(p50) & asset_type == 3

* baseline is equal-weighted regression --- all obs with same weight
gen tmp_weight = 1

**********

drop if datem == tm(2019m12)

*** covid only - raw returns
* equities
gen lag_y = lag_return_cleanprice_eur

reghdfe return_cleanprice_eurW i.IsCrossHeld##i.Dummy_March2020 `controls' [aweight=tmp_weight] if asset_type == 1 & dum_developed == 1, absorb(ui_date) vce(robust)
est store developed_return_1 

reghdfe return_cleanprice_eurW i.IsCrossHeld##i.Dummy_March2020 `controls' [aweight=tmp_weight] if asset_type == 1 & dum_developed == 1, absorb(ui_date ui_isin)  vce(robust)
est store developedIsinFE_return_1 

replace lag_y = lag_alpha_Fama5

reghdfe alpha_Fama5W i.IsCrossHeld##i.Dummy_March2020 `controls' [aweight=tmp_weight] if asset_type == 1 & dum_developed == 1, absorb(ui_date) vce(robust)
est store developed_alpha_1 

reghdfe alpha_Fama5W i.IsCrossHeld##i.Dummy_March2020 `controls' [aweight=tmp_weight] if asset_type == 1 & dum_developed == 1,  absorb(ui_date ui_isin) vce(robust)
est store developedIsinFE_alpha_1  

*** corp bonds
replace lag_y = lag_return_cleanprice_eur

capture reghdfe return_cleanprice_eurW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 2 & dum_developed == 1, absorb(ui_date) vce(robust)
est store developed_return_2

reghdfe return_cleanprice_eurW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 2 & dum_developed == 1, absorb(ui_date ui_isin) vce(robust)
est store developedIsinFE_return_2

replace lag_y = lag_alpha_Bai

capture reghdfe alpha_BaiW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 2 & dum_developed == 1, absorb(ui_date) vce(robust)
est store developed_alpha_2

reghdfe alpha_BaiW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 2 & dum_developed == 1,  absorb(ui_date ui_isin) vce(robust)
est store developedIsinFE_alpha_2

*** sov bonds
replace lag_y = lag_return_cleanprice_eur

capture reghdfe return_cleanprice_eurW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 3 & dum_developed == 1, absorb(ui_date) vce(robust)
est store developed_return_3 

reghdfe return_cleanprice_eurW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 3 & dum_developed == 1, absorb(ui_date ui_isin) vce(robust)
est store developedIsinFE_return_3 

replace lag_y = lag_alpha_Bai

capture reghdfe alpha_BaiW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 3 & dum_developed == 1, absorb(ui_date) vce(robust)
est store developed_alpha_3 

reghdfe alpha_BaiW i.IsCrossHeld##i.Dummy_March2020 `controls' lag_BOND_DURATION [aweight=tmp_weight] if asset_type == 3 & dum_developed == 1,  absorb(ui_date ui_isin) vce(robust)
est store developedIsinFE_alpha_3


restore


******************************** export results ************************************

* returns
esttab developed_return* developedIsinFE_return*  using "temp.csv", keep(1.IsCrossHeld  1.IsCrossHeld#1.Dummy_March2020) ///
b(3) t(3) star(* 0.10 ** 0.05 *** 0.01) /// 
stats(N r2_a, /// 
fmt(%16.0g %9.4g) ///
) nonotes noomitted mtitles label replace 

preserve
clear
insheet using temp.csv
export excel using "${Path}Paper\Tables\Tab6_Security-level effects during March 2020_${sample}.xlsx", sheet("Developed")  sheetmodify   cell(B2)
restore

* alphas
esttab developed_alpha* developedIsinFE_alpha* using "temp.csv", keep(1.IsCrossHeld  1.IsCrossHeld#1.Dummy_March2020) ///
b(3) t(3) star(* 0.10 ** 0.05 *** 0.01) /// 
stats(N r2_a, /// 
fmt(%16.0g %9.4g) ///
) nonotes noomitted mtitles label replace 

preserve
clear
insheet using temp.csv
export excel using "${Path}Paper\Tables\Tab6_Security-level effects during March 2020_${sample}.xlsx", sheet("Developed")  sheetmodify   cell(B12)
restore